/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO.Util;
import Pojo.*;
import java.util.Iterator;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
/**
 *
 * @author An
 */
public class KetquaDAO {
    public static boolean ThemKetQua(Ketqua kq)
    {
        Session session = HibernateUtil.getSessionFactory().openSession();
        boolean flag = true;
        Transaction transaction = null;
        try{
            transaction = session.beginTransaction();
            session.save(kq);
            transaction.commit();
        } catch (HibernateException ex) {
            transaction.rollback();
            System.err.println(ex.getMessages());
            flag = false;
        } finally {
            session.close();
        }
        return flag;
    }
    
    public static List<Ketqua> LayKetQuaTheoMaDe(int madethi)
    {
        List<Ketqua> kq = null;
        Session session = HibernateUtil.getSessionFactory().openSession();
        try { 
            
            //String hql2 = "from Sanpham sp join sp.diadiem dd where dd.ddId =:madiadiem"; 
            
            String hql = "select kq from Ketqua kq join kq.dethi dt where dt.maDeThi =:madethi"; 
            //String hql1 = "from Ketqua kq where kq.dethi = :madethi"; 
            Query query = session.createQuery(hql);
            query.setParameter("madethi", madethi);
            kq = query.list(); 
        } catch (HibernateException ex) { 
            System.err.println(ex); 
        } finally { 
            session.close(); 
        } 
        return kq;
    }
    public static List<Ketqua> LayDanhSachKetQuaThi(int batDau, int soDongTrongMotTrang) {
        List<Ketqua> kq = null;
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            //String hql = "from Ketqua kq , Thisinh ts where ts.maThiSinh = kq.thisinh.maThiSinh order by kq.thoiGianBatDau";
            String hql = "from Ketqua kq order by kq.thoiGianBatDau desc ";
            Query query = session.createQuery(hql);
            query.setFirstResult(batDau);
            query.setMaxResults(soDongTrongMotTrang);
            kq = query.list();
        } catch (HibernateException ex) {
            return null;
        } finally {
            session.close();
        }
        return kq;
    }

    public static int LayDanhSachKetQuaThi() {
        int kq = 0;
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            //String hql = "from Ketqua kq , Thisinh ts where ts.maThiSinh = kq.thisinh.maThiSinh order by kq.thoiGianBatDau";
            String hql = "select count(*) from Ketqua kq group by kq.maKetQua";
            Query query = session.createQuery(hql);
            for (Iterator it = query.iterate(); it.hasNext();it.next()) {
                
                kq++;
            }
        } catch (HibernateException ex) {
            return 0;
        } finally {
            session.close();
        }
        return kq;
    }
    
    public static List<Ketqua> LayDanhSachKetQuaThiTheoCMND(String cmnd,int batDau, int soDongTrongMotTrang) {
        List<Ketqua> kq = null;
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            //String hql = "from Ketqua kq , Thisinh ts where ts.maThiSinh = kq.thisinh.maThiSinh order by kq.thoiGianBatDau";
            //String hql = "from Ketqua kq join kq.dethi dt where dt.maDeThi =:madethi"; 
            String hql = "select kq from Ketqua kq join kq.thisinh ts where ts.cmnd =:cmnd order by kq.thoiGianBatDau desc ";
            Query query = session.createQuery(hql);
            query.setParameter("cmnd", cmnd);
            query.setFirstResult(batDau);
            query.setMaxResults(soDongTrongMotTrang);
            kq = query.list();
        } catch (HibernateException ex) {
            return null;
        } finally {
            session.close();
        }
        return kq;
    }

    public static int DemSoDongKetQuaThiTheoCMND(String cmnd) {
        int kq = 0;
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            //String hql = "from Ketqua kq , Thisinh ts where ts.maThiSinh = kq.thisinh.maThiSinh order by kq.thoiGianBatDau";
            String hql = "select count(*) from Ketqua kq join kq.thisinh ts where ts.cmnd=:cmnd group by kq.maKetQua";
            
            Query query = session.createQuery(hql);
            query.setParameter("cmnd", cmnd);
            for (Iterator it = query.iterate(); it.hasNext();it.next()) {
                
                kq++;
            }
        } catch (HibernateException ex) {
            return 0;
        } finally {
            session.close();
        }
        return kq;
    }
}
